
-- exec sp_update_finishedgoods_track_productname 'MSP2000-CB-A81' , 'MSP2000-CB-A8', 6, 1529

ALTER     proc sp_update_finishedgoods_track_productname (

@part_number varchar(20), 
@old_part_number varchar(20), 
@track_id int,
@process_step_id int )


as
begin
	declare @temp_markandpack_processStep_id int
	declare @temp_test_processStep_id int
	declare @temp_test_step_name varchar(50)

	if @process_step_id <> -1
	Begin       
		update Mark_FG_Track_Table set Product_name = @part_number where AssemblyTrack_ID = @track_id and FG_ProcessStep_ID  = @process_step_id
		update Inventory_Step_table set IST_Manufacturing_Part_Number = @part_number where processStep_Id = @process_step_id
		select  @temp_markandpack_processStep_id = Mark_Pack_ProcessStep_ID , @temp_test_step_name = Test_Step_Name from Mark_FG_Track_Table where AssemblyTrack_Id = @track_id and Fg_ProcessStep_ID = @process_step_id
	End
	Else
	Begin
		update Mark_FG_Track_Table set Product_name = @part_number where AssemblyTrack_ID = @track_id and Product_name = @old_part_number
		select  @temp_markandpack_processStep_id = Mark_Pack_ProcessStep_ID , @temp_test_step_name = Test_Step_Name from Mark_FG_Track_Table where AssemblyTrack_Id = @track_id and Product_name = @part_number
	End
	--print @temp_markandpack_processStep_id
	--print @temp_test_step_name

	if @temp_markandpack_processStep_id is not null
	begin
		update product set part_Number = @part_number where processStep_Id = @temp_markandpack_processStep_id

	end
	
	if @temp_test_step_name is not null
	begin
		select @temp_test_processStep_id = processStep_Id from Test_Track_Table where AssemblyTrack_Id = @track_id and process_step_type = @temp_test_step_name and Bin_Setup = 0
		if @temp_test_processStep_id is not null
		begin
			update  wip_step_bin_table set WSBT_Part_Number = @part_number where ProcessStep_ID = @temp_test_processStep_id and WSBT_Part_Number = @old_part_number
			-- to update bin substitutables			
			update Bin_Substitute_Table set Part_Number=@part_number where Part_Number = @old_part_number and ProcessStep_ID = @temp_test_processStep_id
			update Bin_Substitute_Table set Part_Number_To_Substitute = @part_number where Part_Number_To_Substitute = @old_part_number and ProcessStep_ID = @temp_test_processStep_id
		end
	end 
	else
	begin
		-- to update bin substitutables			
		update Bin_Substitute_Table set Part_Number=@part_number where Part_Number = @old_part_number
		update Bin_Substitute_Table set Part_Number_To_Substitute = @part_number where Part_Number_To_Substitute = @old_part_number
	end 
	
end
